package dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import util.DBUtil;
import util.Page;

import model.Train;
import model.TrainType;
import dao.TrainTypeDAO;

public class TrainTypeDAOImpl implements TrainTypeDAO {
	public List<TrainType> getAll() {
		DBUtil dbu = new DBUtil();
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		List<TrainType> list = new ArrayList<TrainType>();
		try {
			conn = dbu.getConnection();
			String sql = "select * from TRAINTYPE";
			pstat = conn.prepareStatement(sql);
			rs = pstat.executeQuery();
			while (rs.next()) {
				TrainType tt = new TrainType();
				tt.setId(rs.getInt("ID"));
				tt.setTypename(rs.getString("TYPENAME"));
				list.add(tt);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return list;
	}

	public TrainType getById(int id) {
		DBUtil dbu = new DBUtil();
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		TrainType tt = null;
		try {
			conn = dbu.getConnection();
			String sql = "select * from TRAINTYPE where id=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, id);
			rs = pstat.executeQuery();
			while (rs.next()) {
				tt = new TrainType();
				tt.setId(rs.getInt("ID"));
				tt.setTypename(rs.getString("TYPENAME"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return tt;
	}

	public TrainType getByName(String name) {
		DBUtil dbu = new DBUtil();
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		TrainType tt = null;
		try {
			conn = dbu.getConnection();
			String sql = "select * from TRAINTYPE where typename=?";
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, name);
			rs = pstat.executeQuery();
			while (rs.next()) {
				tt = new TrainType();
				tt.setId(rs.getInt("ID"));
				tt.setTypename(rs.getString("TYPENAME"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return tt;
	}

	public boolean update(TrainType trainType) {
		DBUtil dbu = new DBUtil();
		Connection conn = null;
		PreparedStatement pstat = null;
		try {
			conn = dbu.getConnection();
			String sql = "update TRAINTYPE set typename=? where id=?";
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, trainType.getTypename());
			pstat.setInt(2, trainType.getId());
			pstat.executeQuery();

		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			dbu.closeConnection(conn, pstat, null);
		}
		return true;
	}

	public boolean delete(int id) {
		DBUtil dbu = new DBUtil();
		Connection conn = null;
		PreparedStatement pstat = null;
		try {
			conn = dbu.getConnection();
			String sql = "delete from TRAINTYPE where id=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, id);
			pstat.executeQuery();

		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			dbu.closeConnection(conn, pstat, null);
		}
		return true;
	}

	public boolean add(TrainType traintype) {
		DBUtil dbu = new DBUtil();
		Connection conn = null;
		PreparedStatement pstat = null;
		try {
			conn = dbu.getConnection();
			String sql = "insert into trainType values(seq.nextval,?)";
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, traintype.getTypename());
			pstat.executeQuery();

		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			dbu.closeConnection(conn, pstat, null);
		}
		return true;
	}

	public Page<TrainType> getAll(int pageNo, int pageSize) {
		Page<TrainType> page = new Page<TrainType>(pageNo, pageSize);
		int totalCount = 0;
		DBUtil dbu = new DBUtil();
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		List<TrainType> list = new ArrayList<TrainType>();
		try {
			conn = dbu.getConnection();
			String sql="select count(*) from TrainType";
			pstat=conn.prepareStatement(sql);
			rs=pstat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select s.*,rownum rn from (select * from TRAINTYPE) s where rownum <=?) where rn >=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, page.getPageNo()*pageSize);
			pstat.setInt(2,(page.getPageNo()-1)*pageSize+1);
			rs = pstat.executeQuery();
			while (rs.next()) {
				TrainType tt = new TrainType();
				tt.setId(rs.getInt("ID"));
				tt.setTypename(rs.getString("TYPENAME"));
				list.add(tt);
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return page;
	}
	
}
